﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using POSPlus.DatabaseOperations;
using System.Globalization;

namespace POSPlus.EZBarTender.DatabaseOperations
{
	/// <summary>
	/// Accesses the POSplus_PrintManagerSigns and does several operations with that data.
	/// </summary>
	public class PrintData : DatabaseObject
	{
		private double _youSave;
		private double _priceUom;
		private double _normalPrice;

		#region Properties
		public String Upc { get; private set; }
		public String Desc1 { get; private set; }
		public String Desc2 { get; private set; }
		public String Desc3 { get; private set; }
		public String Desc4 { get; private set; }
		public String Desc5 { get; private set; }
		public String NormalPrice {
			get
			{
				return MoneyConversion(_normalPrice);
			}
			private set
			{
				if (!double.TryParse(value, out _normalPrice))
					_normalPrice = default(double);
			}
		}
		public String PriceUom {
			get 
			{
				return "$" + _priceUom.ToString("0.000");
			}
			private set
			{
				if (!double.TryParse(value, out _priceUom))
					_priceUom = default(double);
			}
		}
		public String Selling { get; private set; }
		public String Size { get; private set; }
		public String Uom { get; private set; }
		public DateTime WeekEnding { get; private set; }
		public String YouSave { 
			get{return MoneyConversion(_youSave);}
			private set
			{
				if (!double.TryParse(value, out _youSave))
					_youSave = default(double);
			}
		}
		public String SignType { get; private set; }
		public String SignSize { get; private set; }
		public String JobName { get; private set; }
		public Int32 Copies { get; private set; }
		#endregion

		#region Constructors
		/// <summary>
		/// Initializes a new instance of the POSPlus.EZBarTender.DatabaseOperation.PrintManagerSign class 
		/// when given a string that contains the connection string.
		/// </summary>
		/// <param name="connectionString"></param>
		public PrintData(String connectionString)
		{
			ConnectionString = connectionString;
			PopulateDefault();
		}

		/// <summary>
		/// Intialized a new instance of the POSPlus.EZBarTender.DatabaseOperation.PrintManagerSign class
		/// with the connection string, upc, and weekending.  It accesses the database and loads the results
		/// of the query with the obtained record.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="upc"></param>
		/// <param name="weekEnding"></param>
		public PrintData(String connectionString, String upc)
		{
			
			ConnectionString = connectionString;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT Upc, Desc1, Desc2, Desc3, Desc4, Desc5, NormalPrice, " +
					"PriceUom, Selling, Size, Uom, WeekEnding, YouSave, SignType, SignSize FROM POSplus_PrintManagerSigns " +
					"WHERE Upc = @Upc", connection))
				{
					command.Parameters.AddWithValue("@Upc", upc);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							Upc = Convert.ToString(reader["Upc"]);
							Desc1 = Convert.ToString(reader["Desc1"]);
							Desc2 = Convert.ToString(reader["Desc2"]);
							Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
							Desc4 = Convert.ToString(reader["Desc4"]);
							Desc5 = Convert.ToString(reader["Desc5"]);
							NormalPrice = FromDB<String>(reader["NormalPrice"]);
							PriceUom = FromDB<String>(reader["PriceUom"]);
							Selling = Convert.ToString(reader["Selling"]);
							Size = Convert.ToString(reader["Size"]);
							Uom = Convert.ToString(reader["Uom"]);
							WeekEnding = Convert.ToDateTime(reader["WeekEnding"]);
							YouSave = FromDB<String>(reader["YouSave"]);
							SignType = Convert.ToString(reader["SignType"]);
							SignSize = Convert.ToString(reader["SignSize"]);
							JobName = Convert.ToString(reader["JobName"]);
							Copies = FromDB<Int32>(reader["Copies"]);
						}
						else
						{
							PopulateDefault();
						}
						reader.Close();
						connection.Close();
					}
				}
			}
		}
		#endregion

		#region Methods
		/// <summary>
		/// Sets the properties to be the defaults for the database.
		/// </summary>
		protected override void PopulateDefault()
		{
			Upc = "";
			Desc1 = "";
			Desc2 = "";
			Desc3 = "";
			Desc4 = "";
			Desc5 = "";
			NormalPrice = "";
			PriceUom = "";
			Selling = "";
			Size = "";
			Uom = "";
			WeekEnding = new DateTime(1900, 1, 1);
			YouSave = "";
			SignType = "";
			SignSize = "";
			JobName = "";
			Copies = 0;
		}

		/// <summary>
		/// Inserts the current object state into the SQL database.
		/// </summary>
		public override Boolean Create()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("INSERT INTO POSplus_PrintManagerSigns " +
					"(Upc, Desc1, Desc2, Desc3, Desc4, Desc5, NormalPrice, PriceUom, Selling, Size, Uom, WeekEnding, YouSave, " +
					"SignType, SignSize, JobName) VALUES (@Upc, @Desc1, @Desc2, @Desc3, @Desc4, @Desc5, @NormalPrice, @PriceUom, @Selling, @Size, @Uom, @WeekEnding, @YouSave, " +
					"@SignType, @SignSize, @JobName)", connection))
				{
					command.Parameters.AddWithValue("@Upc", DatabaseObject.ToDB(Upc));
					command.Parameters.AddWithValue("@Desc1", DatabaseObject.ToDB(Desc1));
					command.Parameters.AddWithValue("@Desc2", DatabaseObject.ToDB(Desc2));
					command.Parameters.AddWithValue("@Desc3", DatabaseObject.ToDB(Desc3));
					command.Parameters.AddWithValue("@Desc4", DatabaseObject.ToDB(Desc4));
					command.Parameters.AddWithValue("@Desc5", DatabaseObject.ToDB(Desc5));
					command.Parameters.AddWithValue("@NormalPrice", DatabaseObject.ToDB(_normalPrice));
					command.Parameters.AddWithValue("@PriceUom", DatabaseObject.ToDB(_priceUom));
					command.Parameters.AddWithValue("@Selling", DatabaseObject.ToDB(Selling));
					command.Parameters.AddWithValue("@Size", DatabaseObject.ToDB(Size));
					command.Parameters.AddWithValue("@Uom", DatabaseObject.ToDB(Uom));
					command.Parameters.AddWithValue("@WeekEnding", DatabaseObject.ToDB(WeekEnding));
					command.Parameters.AddWithValue("@YouSave", DatabaseObject.ToDB(_youSave));
					command.Parameters.AddWithValue("@SignType", DatabaseObject.ToDB(SignType));
					command.Parameters.AddWithValue("@SignSize", DatabaseObject.ToDB(SignSize));
					command.Parameters.AddWithValue("@JobName", DatabaseObject.ToDB(JobName));
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
				}
			}
			return rowsAffected > 0;
		}

		/// <summary>
		/// Updates the database record with the current object state.
		/// </summary>
		public override Boolean Update()
		{
			if (Upc == "") return false;
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("UPDATE POSplus_PrintManagerSign SET " +
					"Upc = @Upc, Desc1 = @Desc1, Desc2 = @Desc2, Desc3 = @Desc3, Desc4 = @Desc4, Desc5 = @Desc5, " +
					"Uom = @Uom, WeekEnding = @WeekEnding, YouSave = @YouSave, SignType = @SignType, SignSize = @SignSize, " +
					"JobName = @JobName", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@Desc1", Desc1);
					command.Parameters.AddWithValue("@Desc2", Desc2);
					command.Parameters.AddWithValue("@Desc3", Desc3);
					command.Parameters.AddWithValue("@Desc4", Desc4);
					command.Parameters.AddWithValue("@Desc5", Desc5);
					command.Parameters.AddWithValue("@NormalPrice", _normalPrice);
					command.Parameters.AddWithValue("@PriceUom", _priceUom);
					command.Parameters.AddWithValue("@Selling", Selling);
					command.Parameters.AddWithValue("@Size", Size);
					command.Parameters.AddWithValue("@Uom", Uom);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					command.Parameters.AddWithValue("@YouSave", _youSave);
					command.Parameters.AddWithValue("@SignType", SignType);
					command.Parameters.AddWithValue("@SignSize", SignSize);
					command.Parameters.AddWithValue("@JobName", JobName);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
				}
			}
			return rowsAffected > 0;
		}

		/// <summary>
		/// Deletes the object from the database.
		/// </summary>
		public override Boolean Delete()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("DELETE FROM POSplus_PrintManagerSigns " +
					"WHERE Upc = @Upc AND WeekEnding = @WeekEnding", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
					Upc = "";
					WeekEnding = new DateTime(1900, 1, 1);
				}
				connection.Close();
			}
			return rowsAffected > 0;
		}

		public static void RemoveWithJobNameEqualTo(string jobName, String connectionString)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("DELETE FROM POSplus_PrintManagerSigns " +
					"WHERE JobName = @JobName", connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					command.ExecuteNonQuery();
					connection.Close();
				}
			}
		}

		/// <summary>
		/// Retrieves all records order by Job Name then Upc.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		public static List<PrintData> GetAllOrderedByJobNameUpc(String connectionString)
		{
			List<PrintData> result = new List<PrintData>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerSigns ORDER BY " +
					"JobName, Upc", connection))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							PrintData printManagerSign = new PrintData(connectionString);
							printManagerSign.Desc1 = DatabaseObject.FromDB<String>(reader["Desc1"]);
							printManagerSign.Desc2 = DatabaseObject.FromDB<String>(reader["Desc2"]);
							printManagerSign.Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
							printManagerSign.Desc4 = DatabaseObject.FromDB<String>(reader["Desc4"]);
							printManagerSign.Desc5 = DatabaseObject.FromDB<String>(reader["Desc5"]);
							printManagerSign.JobName = DatabaseObject.FromDB<String>(reader["JobName"]);
							printManagerSign.NormalPrice = DatabaseObject.FromDB<Double>(reader["NormalPrice"]).ToString();
							printManagerSign.PriceUom = DatabaseObject.FromDB<Double>(reader["PriceUom"]).ToString();
							printManagerSign.Selling = DatabaseObject.FromDB<String>(reader["Selling"]);
							printManagerSign.SignSize = DatabaseObject.FromDB<String>(reader["SignSize"]);
							printManagerSign.SignType = DatabaseObject.FromDB<String>(reader["SignType"]);
							printManagerSign.Size = DatabaseObject.FromDB<String>(reader["Size"]);
							printManagerSign.Uom = DatabaseObject.FromDB<String>(reader["Uom"]);
							printManagerSign.Upc = DatabaseObject.FromDB<String>(reader["Upc"]);
							printManagerSign.WeekEnding = DatabaseObject.FromDB<DateTime>(reader["WeekEnding"]);
							printManagerSign.YouSave = DatabaseObject.FromDB<Double>(reader["YouSave"]).ToString();
							printManagerSign.Copies = FromDB<Int32>(reader["Copies"]);
							result.Add(printManagerSign);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return result;
		}

		/// <summary>
		/// Gets all records with the given Job Name ordered by Upc.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="jobName"></param>
		/// <returns></returns>
		public static List<PrintData> GetJobNamedOrdered(String connectionString, String jobName)
		{
			List<PrintData> result = new List<PrintData>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerSigns WHERE JobName LIKE @JobName " +
					"ORDER BY JobName, Upc", connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{

							PrintData sign = new PrintData(connectionString);
							sign.Desc1 = Convert.ToString(reader["Desc1"]);
							sign.Desc2 = Convert.ToString(reader["Desc2"]);
							sign.Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
							sign.Desc4 = Convert.ToString(reader["Desc4"]);
							sign.Desc5 = Convert.ToString(reader["Desc5"]);
							sign.JobName = Convert.ToString(reader["JobName"]);
							sign.NormalPrice = FromDB<double>(reader["NormalPrice"]).ToString();
							sign.PriceUom = FromDB<double>(reader["PriceUom"]).ToString();
							sign.Selling = Convert.ToString(reader["Selling"]);
							sign.SignSize = Convert.ToString(reader["SignSize"]);
							sign.SignType = Convert.ToString(reader["SignType"]);
							sign.Size = Convert.ToString(reader["Size"]);
							sign.Uom = Convert.ToString(reader["Uom"]);
							sign.Upc = Convert.ToString(reader["Upc"]);
							sign.WeekEnding = Convert.ToDateTime(reader["WeekEnding"]);
							sign.YouSave = FromDB<double>(reader["YouSave"]).ToString();
							sign.Copies = FromDB<Int32>(reader["Copies"]);
							result.Add(sign);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return result;
		}

		/// <summary>
		/// Gets all records.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		public static List<PrintData> GetAll(String connectionString)
		{
			List<PrintData> result = new List<PrintData>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerSigns", connection))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							PrintData sign = new PrintData(connectionString);
							sign.Desc1 = Convert.ToString(reader["Desc1"]);
							sign.Desc2 = Convert.ToString(reader["Desc2"]);
							sign.Desc3 = DatabaseObject.FromDB<String>(reader["Desc3"]);
							sign.Desc4 = Convert.ToString(reader["Desc4"]);
							sign.Desc5 = Convert.ToString(reader["Desc5"]);
							sign.JobName = Convert.ToString(reader["JobName"]);
							sign.NormalPrice = FromDB<String>(reader["NormalPrice"]);
							sign.PriceUom = FromDB<String>(reader["PriceUom"]);
							sign.Selling = Convert.ToString(reader["Selling"]);
							sign.SignSize = Convert.ToString(reader["SignSize"]);
							sign.SignType = Convert.ToString(reader["SignType"]);
							sign.Size = Convert.ToString(reader["Size"]);
							sign.Uom = Convert.ToString(reader["Uom"]);
							sign.Upc = Convert.ToString(reader["Upc"]);
							sign.WeekEnding = Convert.ToDateTime(reader["WeekEnding"]);
							sign.YouSave = FromDB<String>(reader["YouSave"]);
							sign.Copies = FromDB<Int32>(reader["Copies"]);
							result.Add(sign);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return result;
		}

		public static List<PrintData> GetAllOnJobName(String connectionString, String jobName)
		{
			List<PrintData> printData = new List<PrintData>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintData " +
					"WHERE JobName = @JobName ORDER BY JobName, Upc", connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							PrintData data = new PrintData(connectionString);
							data.Copies = FromDB<Int32>(reader["Copies"]);
							data.Desc1 = FromDB<String>(reader["Desc1"]);
							data.Desc2 = FromDB<String>(reader["Desc2"]);
							data.Desc3 = FromDB<String>(reader["Desc3"]);
							data.Desc4 = FromDB<String>(reader["Desc4"]);
							data.Desc5 = FromDB<String>(reader["Desc5"]);
							data.JobName = FromDB<String>(reader["JobName"]);
							data.NormalPrice = FromDB<Double>(reader["NormalPrice"]).ToString();
							data.PriceUom = FromDB<Double>(reader["PriceUom"]).ToString();
							data.Selling = FromDB<String>(reader["Selling"]);
							if (data.JobName.Split(' ').Length > 0)
							{
								data.SignSize = data.JobName.Split(' ')[1];
								data.SignType = data.JobName.Split(' ')[0];
							}
							data.Size = FromDB<String>(reader["Size"]);
							data.Uom = FromDB<String>(reader["Uom"]);
							data.Upc = FromDB<String>(reader["Upc"]);
							data.WeekEnding = FromDB<DateTime>(reader["WeekEnding"]);
							data.YouSave = FromDB<Double>(reader["YouSave"]).ToString();
							printData.Add(data);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return printData;
		}

		private String MoneyConversion(double value)
		{
			if (value < 1)
				return Math.Round(value * 100, 0).ToString() + "\u00A2";
			else
			{
				return value.ToString("0.00");
			}
		}
		#endregion
	}
}
